﻿<TemplateInstance>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=MetadataConnectionString" ComponentName="MetadataConnectionString">
		<ConfigurableProperty Name="Value" Value="Data Source=PS0820;Initial Catalog=ETL_Meta;Integrated Security=SSPI;Provider=SQLNCLI.1;Auto Translate=False;" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=PartialStagingSQLStatement" ComponentName="PartialStagingSQLStatement">
		<ConfigurableProperty Name="Value" Value=" SELECT IndividualId ,LastName1,LastName2,MergedIndividualID,MiddleName1,MiddleName2,MiddleName3,NamePrefix1,NameSuffix,PreferredFirstName,SuppressionID,UpdatedDate,FirstName,FullName,Gender , [GENEVA_Deleted_Flag], [GENEVA_Staging_Extraction_id]  FROM [MSO_Individual] (NOLOCK)" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=PubConnectionString" ComponentName="PubConnectionString">
		<ConfigurableProperty Name="Value" Value="Data Source=PS0820;Initial Catalog=GenevaOne;Integrated Security=SSPI;Provider=SQLNCLI.1;Auto Translate=False;" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=SourceConnectionString" ComponentName="SourceConnectionString">
		<ConfigurableProperty Name="Value" Value="Data Source=ps0820;Initial Catalog=MSO_Source;Integrated Security=SSPI;Provider=SQLNCLI.1;Auto Translate=False;" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=SourceQuery" ComponentName="SourceQuery">
		<ConfigurableProperty Name="Value" Value=" EXECUTE  dbo.GetCustomerData   @Select = 'IndividualId ,LastName1,LastName2,MergedIndividualID,MiddleName1,MiddleName2,MiddleName3,NamePrefix1,NameSuffix,PreferredFirstName,SuppressionID,UpdatedDate,FirstName,FullName,Gender', @From = 'Individual', @Where = '1=1 AND MergedIndividualID=0 AND UpdatedDate &gt; ''30 Dec 1899 00:00:00''' ,@InTableFilter='IndividualId in MSO_IndividualDataSourceTxlat'" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=StagingConnectionString" ComponentName="StagingConnectionString">
		<ConfigurableProperty Name="Value" Value="Data Source=PS0820;Initial Catalog=GenevaStaging;Integrated Security=SSPI;Provider=SQLNCLI.1;Auto Translate=False;" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=StagingSQLStatement" ComponentName="StagingSQLStatement">
		<ConfigurableProperty Name="Value" Value=" SELECT IndividualId ,LastName1,LastName2,MergedIndividualID,MiddleName1,MiddleName2,MiddleName3,NamePrefix1,NameSuffix,PreferredFirstName,SuppressionID,UpdatedDate,FirstName,FullName,Gender , [GENEVA_Deleted_Flag], [GENEVA_Staging_Extraction_id] FROM [MSO_Individual] (NOLOCK) WHERE GENEVA_Staging_Extraction_id=0" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=TableName" ComponentName="TableName">
		<ConfigurableProperty Name="Value" Value="MSO_Individual" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Variable=WorkAreaConnectionString" ComponentName="WorkAreaConnectionString">
		<ConfigurableProperty Name="Value" Value="Data Source=PS0820;Initial Catalog=ETLWorkArea;Integrated Security=SSPI;Provider=SQLNCLI.1;Auto Translate=False;" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Connection=ETLWorkArea" ComponentName="ETLWorkArea">
		<ConfigurableProperty Name="ConnectionString" Value="" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Connection=MetadataConnectionString" ComponentName="MetadataConnectionString">
		<ConfigurableProperty Name="ConnectionString" Value="" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Connection=PubConnectionString" ComponentName="PubConnectionString">
		<ConfigurableProperty Name="ConnectionString" Value="" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Connection=SourceConnectionString" ComponentName="SourceConnectionString">
		<ConfigurableProperty Name="ConnectionString" Value="" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Connection=StagingConnectionString" ComponentName="StagingConnectionString">
		<ConfigurableProperty Name="ConnectionString" Value="" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Get Source query from meta" ComponentName="Get Source query from meta">
		<ConfigurableProperty Name="SqlStatementSource" Value="[GetETL_SourceSystemQuery] 'MSO','Individual',?,0, ?" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Truncate Tables/Executable=Truncate Staging Table" ComponentName="Truncate Staging Table">
		<ConfigurableProperty Name="SqlStatementSource" Value="IF EXISTS(SELECT top 1 * from MSO_Individual) BEGIN TRUNCATE TABLE MSO_Individual END" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Truncate Tables/Executable=Truncate GenevaOne Table" ComponentName="Truncate GenevaOne Table">
		<ConfigurableProperty Name="SqlStatementSource" Value="IF EXISTS(SELECT top 1 * from MSO_Individual) BEGIN TRUNCATE TABLE MSO_Individual END" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Truncate Workarea/Executable=Truncate Workarea" ComponentName="Truncate Workarea">
		<ConfigurableProperty Name="SqlStatementSource" Value="IF EXISTS(SELECT top 1 * from MSO_Individual) BEGIN TRUNCATE TABLE MSO_Individual END" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Full Extract/Executable=Source to Staging/Component=Staging" ComponentName="Staging">
		<ConfigurableProperty Name="OpenRowset" Value="MSO_Individual" />
		<Mapping SourceColumnName="ExtractionProcessID" SourceColumnUsage="True" DestinationColumnName="GENEVA_Staging_Extraction_id" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Full Extract/Executable=Staging to GenevaOne (Full)/Component=GenevaOne" ComponentName="GenevaOne">
		<ConfigurableProperty Name="OpenRowset" Value="MSO_Individual" />
		<Mapping SourceColumnName="GENEVA_Staging_Extraction_id" SourceColumnUsage="True" DestinationColumnName="GENEVA_ONE_Extraction_id" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Source to Workarea/Component=Workarea" ComponentName="Workarea">
		<ConfigurableProperty Name="OpenRowset" Value="MSO_Individual" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=Fetch Delta from WorkArea" ComponentName="Fetch Delta from WorkArea">
		<ConfigurableProperty Name="SqlCommand" Value="SELECT IndividualId ,LastName1,LastName2,MergedIndividualID,MiddleName1,MiddleName2,MiddleName3,NamePrefix1,NameSuffix,PreferredFirstName,SuppressionID,UpdatedDate,FirstName,FullName,Gender FROM MSO_Individual" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=ExtractionID Clone" ComponentName="ExtractionID Clone">
		<ColumnCollection Name="Derived Column Output" Type="Output">
			<Column Name="Derived Column 1">
				<ConfigurableProperty Name="FriendlyExpression" Value="UpdatedDate" />
			</Column>
		</ColumnCollection>
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=OLE DB Destination" ComponentName="OLE DB Destination">
		<ConfigurableProperty Name="OpenRowset" Value="MSO_Individual" />
		<Mapping SourceColumnName="ExtractionProcessID" SourceColumnUsage="True" DestinationColumnName="GENEVA_Staging_Extraction_id" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=WorkArea to Staging/Component=Staging Command" ComponentName="Staging Command">
		<ConfigurableProperty Name="SqlCommand" Value="UPDATE MSO_Individual SET LastName1 = ?, LastName2 = ?, MergedIndividualID = ?, MiddleName1 = ?, MiddleName2 = ?, MiddleName3 = ?, NamePrefix1 = ?, NameSuffix = ?, PreferredFirstName = ?, SuppressionID = ?, UpdatedDate = ?, FirstName = ?, FullName = ?, Gender = ?, GENEVA_Staging_Extraction_id = ? WHERE IndividualId = ? AND UpdatedDate &lt;&gt; ?" />
		<Mapping SourceColumnName="LastName1" SourceColumnUsage="True" DestinationColumnName="Param_0" />
		<Mapping SourceColumnName="LastName2" SourceColumnUsage="True" DestinationColumnName="Param_1" />
		<Mapping SourceColumnName="MergedIndividualID" SourceColumnUsage="True" DestinationColumnName="Param_2" />
		<Mapping SourceColumnName="MiddleName1" SourceColumnUsage="True" DestinationColumnName="Param_3" />
		<Mapping SourceColumnName="MiddleName2" SourceColumnUsage="True" DestinationColumnName="Param_4" />
		<Mapping SourceColumnName="MiddleName3" SourceColumnUsage="True" DestinationColumnName="Param_5" />
		<Mapping SourceColumnName="NamePrefix1" SourceColumnUsage="True" DestinationColumnName="Param_6" />
		<Mapping SourceColumnName="NameSuffix" SourceColumnUsage="True" DestinationColumnName="Param_7" />
		<Mapping SourceColumnName="PreferredFirstName" SourceColumnUsage="True" DestinationColumnName="Param_8" />
		<Mapping SourceColumnName="SuppressionID" SourceColumnUsage="True" DestinationColumnName="Param_9" />
		<Mapping SourceColumnName="UpdatedDate" SourceColumnUsage="True" DestinationColumnName="Param_10" />
		<Mapping SourceColumnName="FirstName" SourceColumnUsage="True" DestinationColumnName="Param_11" />
		<Mapping SourceColumnName="FullName" SourceColumnUsage="True" DestinationColumnName="Param_12" />
		<Mapping SourceColumnName="Gender" SourceColumnUsage="True" DestinationColumnName="Param_13" />
		<Mapping SourceColumnName="ExtractionProcessID" SourceColumnUsage="True" DestinationColumnName="Param_14" />
		<Mapping SourceColumnName="IndividualId" SourceColumnUsage="True" DestinationColumnName="Param_15" />
		<Mapping SourceColumnName="Derived Column 1" SourceColumnUsage="True" DestinationColumnName="Param_16" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Staging to GenevaOne(Delta)/Component=Lookup" ComponentName="Lookup">
		<ConfigurableProperty Name="SqlCommand" Value="SELECT IndividualId FROM MSO_Individual (NOLOCK)" />
		<ColumnCollection Name="Lookup Input" Type="Input">
			<Column Name="IndividualId">
				<ConfigurableProperty Name="JoinToReferenceColumn" Value="IndividualId" />
			</Column>
		</ColumnCollection>
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Staging to GenevaOne(Delta)/Component=OLE DB Destination" ComponentName="OLE DB Destination">
		<ConfigurableProperty Name="OpenRowset" Value="MSO_Individual" />
		<Mapping SourceColumnName="GENEVA_Staging_Extraction_id" SourceColumnUsage="True" DestinationColumnName="GENEVA_ONE_Extraction_id" />
	</ConfigurableComponent>
	<ConfigurableComponent ComponentUri="Package=Load_MSOIndividualProfile/Executable=Get Source table query/Executable=Delta Extract/Executable=Staging to GenevaOne(Delta)/Component=Pub Command" ComponentName="Pub Command">
		<ConfigurableProperty Name="SqlCommand" Value="UPDATE MSO_Individual SET LastName1 = ?, LastName2 = ?, MergedIndividualID = ?, MiddleName1 = ?, MiddleName2 = ?, MiddleName3 = ?, NamePrefix1 = ?, NameSuffix = ?, PreferredFirstName = ?, SuppressionID = ?, UpdatedDate = ?, FirstName = ?, FullName = ?, Gender = ?, GENEVA_ONE_Extraction_id = ? WHERE IndividualId = ?" />
		<Mapping SourceColumnName="LastName1" SourceColumnUsage="True" DestinationColumnName="Param_0" />
		<Mapping SourceColumnName="LastName2" SourceColumnUsage="True" DestinationColumnName="Param_1" />
		<Mapping SourceColumnName="MergedIndividualID" SourceColumnUsage="True" DestinationColumnName="Param_2" />
		<Mapping SourceColumnName="MiddleName1" SourceColumnUsage="True" DestinationColumnName="Param_3" />
		<Mapping SourceColumnName="MiddleName2" SourceColumnUsage="True" DestinationColumnName="Param_4" />
		<Mapping SourceColumnName="MiddleName3" SourceColumnUsage="True" DestinationColumnName="Param_5" />
		<Mapping SourceColumnName="NamePrefix1" SourceColumnUsage="True" DestinationColumnName="Param_6" />
		<Mapping SourceColumnName="NameSuffix" SourceColumnUsage="True" DestinationColumnName="Param_7" />
		<Mapping SourceColumnName="PreferredFirstName" SourceColumnUsage="True" DestinationColumnName="Param_8" />
		<Mapping SourceColumnName="SuppressionID" SourceColumnUsage="True" DestinationColumnName="Param_9" />
		<Mapping SourceColumnName="UpdatedDate" SourceColumnUsage="True" DestinationColumnName="Param_10" />
		<Mapping SourceColumnName="FirstName" SourceColumnUsage="True" DestinationColumnName="Param_11" />
		<Mapping SourceColumnName="FullName" SourceColumnUsage="True" DestinationColumnName="Param_12" />
		<Mapping SourceColumnName="Gender" SourceColumnUsage="True" DestinationColumnName="Param_13" />
		<Mapping SourceColumnName="GENEVA_Staging_Extraction_id" SourceColumnUsage="True" DestinationColumnName="Param_14" />
		<Mapping SourceColumnName="IndividualId" SourceColumnUsage="True" DestinationColumnName="Param_15" />
	</ConfigurableComponent>
</TemplateInstance>